﻿using System;
using Npgsql;

namespace TeamProject
{
    public static class DBConnection
    {
        public static string database = "timak";
        public static string server = "localhost";
        public static int port = 5432;
        public static string uid = "postgres";
        public static string pw = "timak";

        public static string connectString = "database=" + database + ";server=" + server + ";port=" + port + ";UID=" + uid + ";password=" + pw;
    }


    public static class DBApi
    {
        private static string sql = "";

        static NpgsqlConnection con;
        static NpgsqlCommand cmd;
        static NpgsqlDataReader dr;

        private static NpgsqlDataReader query(string sql)
        {
            try
            {
                con = new NpgsqlConnection(DBConnection.connectString);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);

                dr = cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return dr;
        }

        private static GeoObject populateObject(NpgsqlDataReader dr)
        {
            GeoObject geo = new GeoObject();

            geo.Id = dr["ID"].ToString();
            geo.GeoData = dr["geo_data"].ToString();
            geo.SourceID = dr["sourceID"].ToString();
            geo.Name = dr["name"].ToString();
            geo.CategoryText = dr["category_text"].ToString();
            geo.City = dr["city"].ToString();
            geo.Street = dr["street"].ToString();
            geo.Zip = dr["zip"].ToString();
            geo.Url = dr["url"].ToString();
            geo.Description = dr["description"].ToString();
            geo.Country = dr["country"].ToString();
            geo.Author = dr["author"].ToString();
            geo.CategoryId = dr["category_ID"].ToString();

            string ratingUp = dr["rating_up"].ToString();
            string ratingDown = dr["rating_down"].ToString();

            if (ratingUp == null || ratingUp == "")
                geo.RatingUp = 0;
            else
                geo.RatingUp = Convert.ToInt32(ratingUp);

            if (ratingDown == null || ratingDown == "")
                geo.RatingDown = 0;
            else
                geo.RatingDown = Convert.ToInt32(ratingDown);

            string start = dr["datetime_start"].ToString();
            string end = dr["datetime_end"].ToString();

            try
            {
                if (start != null && start != "")
                    geo.Start = Convert.ToDateTime(start);
                if (end != null && end != "")
                    geo.End = Convert.ToDateTime(end);
            }
            catch (Exception e)
            {
                Utils.LogError(e.Message);
            }

            return geo;
        }

        public static GeoObject getObjectData(String name)
        {
            GeoObject geo = null;

            dr = query("SELECT * FROM geo_object WHERE name = '" + name + "';");
            if (dr.HasRows)
            {
                dr.Read();
                geo = populateObject(dr);
            }
            else
            {
                Utils.LogError("GeoObject with name = '" + name + "' not found!");
            }

            return geo;
        }

        public static GeoObject getObjectData(int Id)
        {
            GeoObject geo = new GeoObject();

            dr = query("SELECT * FROM geo_object WHERE \"ID\" = '" + Id.ToString() + "';");
            if (dr.HasRows)
            {
                dr.Read();
                geo = populateObject(dr);
            }
            else
            {
                Utils.LogError("GeoObject with id = '" + Id.ToString() + "' not found!");
            }

            return geo;
        }

        // vracat category_text alebo idcko kategorie (dotiahnut cez idcko?)
        public static string getObjectType(int Id)
        {
            string type = "";

            dr = query("SELECT category_text FROM geo_object WHERE \"ID\" = '" + Id.ToString() + "';");

            if (dr.HasRows)
            {
                dr.Read();
                type = dr["category_text"].ToString();
            }
            else
            {
                Utils.LogError("GeoObject with id = '" + Id.ToString() + "' not found!");
            }

            if (type == "" || type == null)
                type = "object";

            return type;
        }

        public static string getObjectType(string name)
        {
            string type = "";

            dr = query("SELECT category_text FROM geo_object WHERE \"name\" = '" + name + "';");

            if (dr.HasRows)
            {
                dr.Read();
                type = dr["category_text"].ToString();
            }

            if (type == "" || type == null)
                type = "object";

            return type;
        }



        #region AutoComplete

        public static String[] getObjects(String searchString, int count)
        {
            dr = query("SELECT DISTINCT lower(name) as name FROM geo_object WHERE name LIKE '%" + searchString + "%' LIMIT " + count + ";");
            String[] result = new String[0];


            while (dr.Read())
            {
                Array.Resize<String>(ref result, result.Length + 1);
                result[result.Length - 1] = dr["name"].ToString();
            }

            return result;
        }

        public static String[] getConcepts(String searchString, int count)
        {
            dr = query("SELECT lower(type) as type FROM concepts WHERE type LIKE '%" + searchString + "%' LIMIT " + count + ";");
            String[] result = new String[0];

            while (dr.Read())
            {
                Array.Resize<String>(ref result, result.Length + 1);
                result[result.Length - 1] = dr["type"].ToString();
            }

            return result;
        }

        public static String[] getCities(String searchString, int count)
        {
            //returns cities that match searchString, size of array is number of results
            dr = query("SELECT DISTINCT lower(city) as city FROM geo_object WHERE city LIKE '%" + searchString + "%' LIMIT " + count + ";");
            string[] result = new string[0];

            while (dr.Read())
            {
                Array.Resize<String>(ref result, result.Length + 1);
                result[result.Length - 1] = dr["city"].ToString();
            }

            return result;
        }

        public static String[] getStreets(String searchString, int count)
        {
            //returns streets that match searchString, size of array is number of results
            dr = query("SELECT DISTINCT lower(street) as street FROM geo_object WHERE street LIKE '%" + searchString + "%' LIMIT " + count + ";");
            String[] result = new String[0];

            while (dr.Read())
            {
                Array.Resize<String>(ref result, result.Length + 1);
                result[result.Length - 1] = dr["street"].ToString();
            }

            return result;
        }

        public static GeoObject[] getObjectsInRange(Double[] point, double radius, int categoryID)
        {
            string x = point[0].ToString().Replace(',', '.');
            string y = point[1].ToString().Replace(',', '.');
            string rad = radius.ToString().Replace(',', '.');

            /* select * from geo_object where point '(48,25)' @ circle '((48,21),3)'; - point is in circle */
            dr = query("select * from geo_object where geo_data @ circle '((" + x + "," + y + ")," + rad + ")' and \"category_ID\" = " + categoryID + ";");
            GeoObject[] objects = new GeoObject[0];

            while (dr.Read())
            {
                Array.Resize<GeoObject>(ref objects, objects.Length + 1);
                objects[objects.Length - 1] = populateObject(dr);
            }

            return objects;
        }

        #endregion
    }
}